package org.jeecg.modules.demo.xxljob;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;

import javax.imageio.ImageIO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;

public class OutExcelServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        this.doPost(request, response);
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String OnputimagePath = null;

        /***********************************************使用poi实现excel模版 导出excel********************/
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\opt\\template\\软件运维模板.xlsx"));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheet("Sheet1");
            HSSFRow row = sheet.getRow(2);
            HSSFCell cell = row.getCell((short) 2);
            if (cell == null){
                cell = row.createCell((short) 2);
            }
//            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellType(CellType.STRING);
            cell.setCellValue("12321212");
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            String InputimagePath = "D:\\opt\\upFiles\\temp\\02_1667184598543.jpg";
            BufferedImage bufferImg = ImageIO.read(new File(InputimagePath));
            ImageIO.write(bufferImg,"JPG",byteArrayOut);
            //设置图片大小，位置
            HSSFClientAnchor anchor = new HSSFClientAnchor(5,0,500,122,(short) 0, 5,(short)10,15);
            //创建
            HSSFPatriarch patri = sheet.createDrawingPatriarch();
            patri.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // 输出文件
            OnputimagePath = "D:\\opt\\poi\\result2.xlsx";
            FileOutputStream fileOut = new FileOutputStream(OnputimagePath);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
//        // 检查文件是否存在
//        File obj = new File(OnputimagePath);
//        if (!obj.exists()) {
//            response.setContentType("text/html;charset=GBK");
//            response.getWriter().print("指定文件不存在！");
//          return;
//        }
//        // 读取文件名：用于设置客户端保存时指定默认文件名
//        int index = OnputimagePath.lastIndexOf("\\"); // 前提：传入的path字符串以“\”表示目录分隔符
//        String fileName = OnputimagePath.substring(index + 1);
//        // 写流文件到前端浏览器
//        ServletOutputStream out = response.getOutputStream();
//        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//        BufferedInputStream bis = null;
//        BufferedOutputStream bos = null;
//        try {
//          bis = new BufferedInputStream(new FileInputStream(OnputimagePath));
//          bos = new BufferedOutputStream(out);
//          byte[] buff = new byte[2048];
//          int bytesRead;
//          while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
//            bos.write(buff, 0, bytesRead);
//          }
//        } catch (IOException e) {
//          throw e;
//        } finally {
//          if (bis != null)
//            bis.close();
//          if (bos != null)
//            bos.close();
//        }
    }

    public static void main(String[] args) {
        String OnputimagePath = null;

        /***********************************************使用poi实现excel模版 导出excel********************/
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\opt\\template\\软件运维模板2.xls"));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheet("Sheet1");
            HSSFRow row = sheet.getRow(2);
            HSSFCell cell = row.getCell((short) 2);
            if (cell == null){
                System.out.println("cell为空");
                cell = row.createCell((short) 2);
            }
            if (cell != null){
                System.out.println("cell不为空");
                String stringCellValue = cell.getStringCellValue();
                System.out.println("stringCellValue = " + stringCellValue);
                cell.setCellType(CellType.NUMERIC);
            }
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(123);
            HSSFCell cell3 = row.getCell((short) 3);
            if (cell3 == null){
                cell3 = row.createCell((short) 3);
            }
            cell3.setCellType(CellType.NUMERIC);
            cell3.setCellValue(456);
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            String InputimagePath = "D:\\opt\\upFiles\\temp\\02_1667184598543.jpg";
            BufferedImage bufferImg = ImageIO.read(new File(InputimagePath));
            ImageIO.write(bufferImg,"JPG",byteArrayOut);
            //设置图片大小，位置
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, 4, (short) 2, 5);
            //创建
            HSSFPatriarch patri = sheet.createDrawingPatriarch();
            patri.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // 输出文件
            OnputimagePath = "D:\\opt\\poi\\result2.xls";
            FileOutputStream fileOut = new FileOutputStream(OnputimagePath);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
